package rsj.service.account.dynamic;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Component;

/**
 * 财务动态语句
 *
 * @author yuanqinglong
 * @since 2021/7/21 15:18
 */
@Component
public class FinanceDynamicStatement {


    /**
     * 财务列表
     *
     * @param autKey    账号标识
     * @param custSname 客户名称
     * @param custId    客户id
     * @param pageSize  页面大小
     * @param pageNow   页面现在
     * @return {@link String}
     */
    public String financeDynamicList(String autKey, String custSname, String custId, Integer pageSize, Integer pageNow) {
        String sql = "SELECT user_status, cust_id,org_id,cust_sname,aut_keyid,orgp_id,orgp_status,orgp_utime AS update_tm FROM (\n" ;
                // 查询机构账号信息
                sql +=   this.mainSentence(autKey, custSname, custId, pageSize, pageNow);
                // 查询每个机构在合同表中指定顺序排序的状态
                sql += "        ) org LEFT JOIN (\n" +
                "                SELECT od.* FROM (\n" +
                "                        SELECT orgp_id,orgp_org_id,\n" +
                "                        orgp_status,\n" +
                "                        ROW_NUMBER() OVER (PARTITION BY orgp_org_id ORDER BY CASE\n" +
                "                                WHEN orgp_status = '10' THEN 1\n" +
                "                                WHEN orgp_status = '50' THEN 2\n" +
                "                                WHEN orgp_status = '20' THEN 3\n" +
                "                                WHEN orgp_status = '55' THEN 4\n" +
                "                                WHEN orgp_status = '60' THEN 5\n" +
                "                                WHEN orgp_status = '30' THEN 6\n" +
                "                                WHEN orgp_status = '40' THEN 7\n" +
                "                                WHEN orgp_status = '70' THEN 8\n" +
                "                                END) row_id\n" +
                "                        FROM sys.sys_orgp op\n" +
                //"                        GROUP BY orgp_id, orgp_org_id, orgp_status\n" +
                "                ) od WHERE row_id = 1\n" +
                "        ) status ON status.orgp_org_id = org_id\n" +
                 // 查询合同中最近的合同日期
                "        LEFT JOIN (" +
                "    SELECT orgp_org_id,MAX(orgp_utime) AS orgp_utime FROM sys.sys_orgp op GROUP BY orgp_org_id\n" +
                ") time  ON time.orgp_org_id = org_id ORDER BY orgp_utime DESC NULLS LAST OFFSET #{pageNow} LIMIT #{pageSize}";
        return sql;
    }


    /**
     * 主要的句子
     *
     * @param autKey    aut的关键
     * @param custSname cust sname
     * @param custId    cust id
     * @param pageSize  页面大小
     * @param pageNow   页面现在
     * @return {@link String}
     */
    private String mainSentence(String autKey, String custSname, String custId, Integer pageSize, Integer pageNow){
        String sql = "SELECT user_status,CONCAT('RSJ',LPAD(cust_id::text,4,'0')) AS cust_id,org_id,cust_sname,aut_keyid FROM sys.sys_org LEFT JOIN sys.sys_cust ON cust_id = org_cust_id " +
                "LEFT JOIN sys.sys_authon ON org_id = aut_org_id LEFT JOIN sys.sys_user ON user_org_id = org_id WHERE 1=1 ";
        sql += this.fuzzyCondition(autKey, custSname, custId) + " ";
        return sql;
    }

    /**
     * 财务列表统计
     *
     * @param autKey    账号标识
     * @param custSname 客户名称
     * @param custId    客户id
     * @return {@link String}
     */
    public String financeDynamicCount(String autKey, String custSname, String custId) {
        //String sql = "SELECT COUNT(*) FROM sys.sys_org LEFT JOIN sys.sys_cust ON cust_id = org_cust_id " +
        //        "LEFT JOIN sys.sys_authon ON org_id = aut_org_id WHERE 1=1 ";
        String sql = "SELECT COUNT(*) FROM sys.sys_org LEFT JOIN (\n" +
                "     SELECT od.* FROM (\n" +
                "                        SELECT orgp_id,orgp_org_id,\n" +
                "                        orgp_status,\n" +
                "                        ROW_NUMBER() OVER (PARTITION BY orgp_org_id ORDER BY CASE\n" +
                "                                WHEN orgp_status = '10' THEN 1\n" +
                "                                WHEN orgp_status = '50' THEN 2\n" +
                "                                WHEN orgp_status = '20' THEN 3\n" +
                "                                WHEN orgp_status = '55' THEN 4\n" +
                "                                WHEN orgp_status = '60' THEN 5\n" +
                "                                WHEN orgp_status = '30' THEN 6\n" +
                "                                WHEN orgp_status = '40' THEN 7\n" +
                "                                WHEN orgp_status = '70' THEN 8\n" +
                "                                END) row_id\n" +
                "                        FROM sys.sys_orgp op\n" +
                //"                        GROUP BY orgp_id,orgp_org_id, orgp_status\n" +
                "                ) od WHERE row_id = 1\n" +
                "    ) a ON  orgp_org_id = org_id LEFT JOIN sys.sys_cust\n" +
                "    ON cust_id = org_cust_id LEFT JOIN sys.sys_authon ON org_id = aut_org_id WHERE 1=1 ";
        return sql + this.fuzzyCondition(autKey, custSname, custId);
    }


    /**
     * 模糊的条件
     *
     * @param autKey    aut的关键
     * @param custSname cust sname
     * @param custId    cust id
     * @return {@link String}
     */
    private String fuzzyCondition(String autKey, String custSname, String custId) {
        String sql = "";
        if (StringUtils.isNotBlank(autKey)) {
            sql += " AND aut_keyid LIKE #{%autKey%}";
        }
        if (StringUtils.isNotBlank(custSname)) {
            sql += " AND cust_sname LIKE #{%custSname%}";
        }
        if (StringUtils.isNotBlank(custId)) {
            sql += " AND CONCAT('RSJ',LPAD(cust_id::text,4,'0')) LIKE #{%custId%}";
        }
        return sql;
    }
}
